﻿using System;
using System.Collections;
using System.Linq;
using System.Text;
using System.Web;
using System.Net;
using System.Data;
using System.Data.SqlClient;
using Facebook;
using EsaProjectRest.Service;
using System.Collections.Generic;
using System.Dynamic;

namespace EsaProjectRest
{
    public class EsaInsight
    {
        public float ApplauseRate(string id)
        {
            ServiceRest service1 = new ServiceRest();            
            service1.OpenConnection();

            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT SUM(N_Likes) AS Somma, COUNT(N_Likes) AS Tot_Post FROM  Post WHERE (Id_user = '"+id+"') AND ((SocialNetwork = 'Facebook') OR (SocialNetwork = 'Google'))");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            try
            {
                //memorize id
                float somma = float.Parse(vds.Tables[0].Rows[0]["Somma"].ToString());
                float tot_post = float.Parse(vds.Tables[0].Rows[0]["Tot_Post"].ToString());
                float applause = somma / tot_post;              
                System.Diagnostics.Debug.Print("Applause: " + applause.ToString());
                //vda.Dispose();
                return applause;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);                
            }
            return 0;
        }

        public float ApplauseRateFB(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();

            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT SUM(N_Likes) AS Somma, COUNT(N_Likes) AS Tot_Post FROM  Post WHERE (Id_user = '" + id + "') AND (SocialNetwork = 'Facebook')");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            try
            {
                //memorize id
                float somma = float.Parse(vds.Tables[0].Rows[0]["Somma"].ToString());
                float tot_post = float.Parse(vds.Tables[0].Rows[0]["Tot_Post"].ToString());
                float applause = somma / tot_post;
                System.Diagnostics.Debug.Print("Applause: " + applause.ToString());
                //vda.Dispose();
                return applause;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            return 0;
        }

        public float ApplauseRateGO(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();

            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT SUM(N_Likes) AS Somma, COUNT(N_Likes) AS Tot_Post FROM  Post WHERE (Id_user = '" + id + "') AND (SocialNetwork = 'Google')");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            try
            {
                //memorize id
                float somma = float.Parse(vds.Tables[0].Rows[0]["Somma"].ToString());
                float tot_post = float.Parse(vds.Tables[0].Rows[0]["Tot_Post"].ToString());
                float applause = somma / tot_post;
                System.Diagnostics.Debug.Print("Applause: " + applause.ToString());
                //vda.Dispose();
                return applause;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            return 0;
        }

        public float ConversationRate(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();

            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT SUM(N_Comments) AS Somma, COUNT(N_Comments) AS Tot_Post FROM  Post WHERE (Id_user = '" + id + "') AND ((SocialNetwork = 'Facebook') OR (SocialNetwork = 'Google'))");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            try
            {
                //memorize id
                float somma = float.Parse(vds.Tables[0].Rows[0]["Somma"].ToString());
                float tot_post = float.Parse(vds.Tables[0].Rows[0]["Tot_Post"].ToString());
                float conversation = somma / tot_post;
                System.Diagnostics.Debug.Print("Applause: " + conversation.ToString());
                //vda.Dispose();
                return conversation;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            return 0;
        }

        public float ConversationRateFB(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();

            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT SUM(N_Comments) AS Somma, COUNT(N_Comments) AS Tot_Post FROM  Post WHERE (Id_user = '" + id + "') AND (SocialNetwork = 'Facebook')");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            try
            {
                //memorize id
                float somma = float.Parse(vds.Tables[0].Rows[0]["Somma"].ToString());
                float tot_post = float.Parse(vds.Tables[0].Rows[0]["Tot_Post"].ToString());
                float conversation = somma / tot_post;
                System.Diagnostics.Debug.Print("Applause: " + conversation.ToString());
                //vda.Dispose();
                return conversation;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            return 0;
        }

        public float ConversationRateGO(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();

            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT SUM(N_Comments) AS Somma, COUNT(N_Comments) AS Tot_Post FROM  Post WHERE (Id_user = '" + id + "') AND (SocialNetwork = 'Google')");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            try
            {
                //memorize id
                float somma = float.Parse(vds.Tables[0].Rows[0]["Somma"].ToString());
                float tot_post = float.Parse(vds.Tables[0].Rows[0]["Tot_Post"].ToString());
                float conversation = somma / tot_post;
                System.Diagnostics.Debug.Print("Applause: " + conversation.ToString());
                //vda.Dispose();
                return conversation;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            return 0;
        }

        public float AmplificationRate(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();

            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT SUM(N_Shares) AS Somma, COUNT(N_Shares) AS Tot_Post FROM  Post WHERE (Id_user = '" + id + "') AND ((SocialNetwork = 'Twitter') OR (SocialNetwork = 'Google'))");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            try
            {
                //memorize id
                float somma = float.Parse(vds.Tables[0].Rows[0]["Somma"].ToString());
                float tot_post = float.Parse(vds.Tables[0].Rows[0]["Tot_Post"].ToString());
                float amplification = somma / tot_post;
                System.Diagnostics.Debug.Print("Applause: " + somma.ToString());
                System.Diagnostics.Debug.Print("Applause: " + tot_post.ToString());
                //vda.Dispose();
                return amplification;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            return 0;
        }

        public float AmplificationRateTW(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();

            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT SUM(N_Shares) AS Somma, COUNT(N_Shares) AS Tot_Post FROM  Post WHERE (Id_user = '" + id + "') AND (SocialNetwork = 'Twitter')");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            try
            {
                //memorize id
                float somma = float.Parse(vds.Tables[0].Rows[0]["Somma"].ToString());
                float tot_post = float.Parse(vds.Tables[0].Rows[0]["Tot_Post"].ToString());
                float amplification = somma / tot_post;
                System.Diagnostics.Debug.Print("Applause: " + somma.ToString());
                System.Diagnostics.Debug.Print("Applause: " + tot_post.ToString());
                //vda.Dispose();
                return amplification;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            return 0;
        }

        public float AmplificationRateGO(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();

            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT SUM(N_Shares) AS Somma, COUNT(N_Shares) AS Tot_Post FROM  Post WHERE (Id_user = '" + id + "') AND (SocialNetwork = 'Google')");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            try
            {
                //memorize id
                float somma = float.Parse(vds.Tables[0].Rows[0]["Somma"].ToString());
                float tot_post = float.Parse(vds.Tables[0].Rows[0]["Tot_Post"].ToString());
                float amplification = somma / tot_post;
                System.Diagnostics.Debug.Print("Applause: " + somma.ToString());
                System.Diagnostics.Debug.Print("Applause: " + tot_post.ToString());
                //vda.Dispose();
                return amplification;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            return 0;
        }


        public string[] InteractionRate(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();
            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT COUNT(Id) AS tot_id FROM  Post WHERE (Id_user = '"+id+"')"); 
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            int tot = 0;
            try
            {
                tot = int.Parse(vds.Tables[0].Rows[0]["tot_id"].ToString());
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            
            string[] interaction = new string[tot];
            System.Diagnostics.Debug.Print("TOT: " + tot.ToString());   
            
            DataSet vds2 = new DataSet();
            string vsql2 = string.Format("SELECT Message, Date, SocialNetwork,N_Likes,N_Comments,N_Shares, N_Likes + N_Comments + N_Shares AS somma FROM  Post WHERE (Id_user = '" + id + "') ORDER BY Date DESC");            
            SqlCommand vCom2 = new SqlCommand(vsql2, service1.conn);
            SqlDataAdapter vda2 = new SqlDataAdapter(vCom2);
            vda2.Fill(vds2);
            for (int i = 0; i < tot; i++)
            {
                try
                {
                    interaction[i] = vds2.Tables[0].Rows[i]["Message"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["Date"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["SocialNetwork"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["N_Likes"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["N_Comments"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["N_Shares"].ToString() + "+-+" + vds2.Tables[0].Rows[i]["somma"].ToString();
                    interaction[i] = HttpUtility.HtmlDecode(interaction[i]);
                    System.Diagnostics.Debug.Print("a: " + interaction[i]);
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                }
            }
            return interaction;
        }

        public float Engagement(string id, string data)
        {
            char[] split = { '/' };
            string[] data_modificata = data.ToString().Split(split);
            string giorno_inizio = data_modificata[0];
            string giorno_fine = data_modificata[0];
            string mese = data_modificata[1];
            string anno = data_modificata[2];

            string data_sql = anno+"-"+mese+"-"+giorno_inizio+" 00:00:00";
            string data_sql2 = anno + "-" + mese + "-" + giorno_fine + " 23:59:59";
            
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();
            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT Facebook + Twitter AS somma FROM  Liker WHERE (Id = '"+id+"')");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            float fan = 0;
            float somma = -1;
            float engagement = 0;
            
            try
            {
                fan = float.Parse(vds.Tables[0].Rows[0]["somma"].ToString());
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            System.Diagnostics.Debug.Print("fan: " + fan);

            
            try
            {
                DataSet vds2 = new DataSet();
                string vsql2 = string.Format("SELECT SUM(N_Likes * 1 + N_Comments * 3 + N_Shares * 2) AS somma FROM  Post WHERE (Id_user = '" + id + "') AND (SocialNetwork = 'Facebook' OR SocialNetwork = 'Twitter') AND (Date >= CONVERT(DATETIME, '" + data_sql + "', 102)) AND (Date <= CONVERT(DATETIME, '" + data_sql2 + "', 102))");
                System.Diagnostics.Debug.Print("fan: " + vsql2);

                SqlCommand vCom2 = new SqlCommand(vsql2, service1.conn);
                SqlDataAdapter vda2 = new SqlDataAdapter(vCom2);
                vda2.Fill(vds2);
            
                somma = float.Parse(vds2.Tables[0].Rows[0]["somma"].ToString());
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
                if (somma == -1)
                {
                    somma = 0;
                }
                else
                {
                    return 0;
                }
            }
            
            System.Diagnostics.Debug.Print("somma: " + somma);
            engagement = somma / fan;
            return engagement;
        }

        public string[] Engagement_28(string id)
        {           
            string[] engagement = new string[28];
            int month_attuale = (int)System.DateTime.Now.Month;
            int day_attuale = (int)System.DateTime.Now.Day;
            int year_attuale = (int)System.DateTime.Now.Year;
            
            int contatore = 0;
            float minimo = -1;
            float massimo = 0;
            float range = 0;

            char[] split = { 'T' };
            float value = 0;
            while(contatore < 28){
                string data = day_attuale.ToString() + "/" + month_attuale.ToString() + "/" + year_attuale.ToString();
                System.Diagnostics.Debug.Print("Cont: " + contatore);
                System.Diagnostics.Debug.Print("Giorno: " + data);
                if (day_attuale == 1)
                {
                    day_attuale = 31;
                    if (month_attuale == 1)
                    {
                        month_attuale = 12;
                        year_attuale -= 1;
                    }
                    else
                    {
                        month_attuale--;
                    }
                }
                value = Engagement(id,data);
                System.Diagnostics.Debug.Print("val: "+value.ToString());
                if (value != -1)
                {
                    if (minimo == -1) 
                    { 
                        minimo = value;
                        System.Diagnostics.Debug.Print("min: " + minimo.ToString());
                    }
                    else
                    {
                        if (minimo > value)
                        {
                            minimo = value;
                            System.Diagnostics.Debug.Print("min: " + minimo.ToString());
                        }
                    }

                    if (massimo == 0)
                    {
                        massimo = value;
                        System.Diagnostics.Debug.Print("mas: " + massimo.ToString());
                    }
                    else
                    {
                        if (massimo < value)
                        {
                            massimo = value;
                            System.Diagnostics.Debug.Print("mas: " + massimo.ToString());
                        }
                    }
                    engagement[contatore] = data + "T" + value;
                    contatore++;
                }
                day_attuale--;                
            }
            range = massimo - minimo;
            
            for (int i = 0; i < 28; i++)
            {
                string[] mod_engagement= engagement[i].Split(split);
                if (range != 0)
                {
                     value = ((float.Parse(mod_engagement[1]) - minimo) / range) * 100;
                }
                else
                {
                    value = 0;
                }
                System.Diagnostics.Debug.Print("val2: "+value.ToString());
                if (value <= 50)
                {
                    engagement[i] = mod_engagement[0] + "+-+" + Math.Round(value,2) + "+-+Low";
                }
                else
                {
                    engagement[i] = mod_engagement[0] + "+-+" + Math.Round(value,2) + "+-+High";
                }
                //System.Diagnostics.Debug.Print("Val " + value.ToString());
                
            }
            //System.Diagnostics.Debug.Print("Min= " + minimo + " Range= " + range);
            return engagement;
        }

        public string[] Passion(string id)
        {
            ServiceRest service1 = new ServiceRest();
            service1.OpenConnection();
            DataSet vds = new DataSet();
            string vsql = string.Format("SELECT COUNT(*) AS Tot FROM  (SELECT TOP (100) PERCENT Tag, SocialNetwork, COUNT(Id) AS Count FROM TagPost WHERE (Id_user = '"+id+"') AND (SocialNetwork = 'Facebook' OR SocialNetwork = 'Twitter') GROUP BY Tag, SocialNetwork ORDER BY Count DESC) AS derivedtbl_1");
            SqlCommand vCom = new SqlCommand(vsql, service1.conn);
            SqlDataAdapter vda = new SqlDataAdapter(vCom);
            vda.Fill(vds);
            int tot = 0;
            try
            {
                //memorize id
                tot = int.Parse(vds.Tables[0].Rows[0]["Tot"].ToString());
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            string[] passion = new string[tot];
            string[] tag = new string[tot];
            string[] service = new string[tot];
            string[] tot_tag = new string[tot];
            DataSet vds2 = new DataSet();
            string vsql2 = string.Format("SELECT Tag, SocialNetwork, COUNT(Id) AS Count FROM  TagPost WHERE (Id_user = '"+id+"') AND (SocialNetwork = 'Facebook' OR SocialNetwork = 'Twitter') GROUP BY Tag, SocialNetwork ORDER BY Count DESC");
            SqlCommand vCom2 = new SqlCommand(vsql2, service1.conn);
            SqlDataAdapter vda2 = new SqlDataAdapter(vCom2);
            vda2.Fill(vds2);            
            try
            {
                for (int i = 0; i < tot; i++ )
                {
                    tag[i] = vds2.Tables[0].Rows[i]["Tag"].ToString();
                    service[i] = vds2.Tables[0].Rows[i]["SocialNetwork"].ToString();
                    tot_tag[i] = vds2.Tables[0].Rows[i]["Count"].ToString();
                }
                float n_tag = 0;
                float media_passion = 0;

                for (int i = 0; i < tot; i++)
                {
                    n_tag += float.Parse(tot_tag[i].ToString());
                }
                for (int i = 0; i < tot; i++)
                {
                    media_passion = (float.Parse(tot_tag[i]) / n_tag)*100;
                    passion[i] = tag[i] +"+-+"+ service[i] +"+-+"+ media_passion;
                }
                
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
            return passion;
        }
    }
}